using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;

using Entity;
using BussisnesException;
using System.Data.OleDb;

namespace DAO
{
	/// <summary>
	/// Clase de acceso a los datos del empleado.
	/// </summary>
	public class TweetDAO:AbstractDAO<Tweet>{
        
        private const string FIELD_ID = "ID";
        private const string FIELD_MESSAGE = "MESSAGE";
        private const string USER_TWEETS = "USER_TWEETS";
        private const string USER_TWEETS_TWEET_FK = "TWEET_FK";
        private const string USER_TWEETS_USER_TAGED = "USER_TAGED";
        private const string USER_TWEETS_IS_OWNER = "OWNER";
        private const string TABLE_NAME = "TWEETS";
        private const string USER_USER_NAME = "USER_NAME";

        private const string INSERT_RELACION = "Insert into " + USER_TWEETS + " (" + USER_TWEETS_TWEET_FK + "," + USER_TWEETS_USER_TAGED + "," + USER_TWEETS_IS_OWNER + ")" +
                    " values(@"+ USER_TWEETS_TWEET_FK+ ", @"+USER_TWEETS_USER_TAGED+", @"+USER_TWEETS_IS_OWNER+" )";

        private const string FIND_TWEETS_USER_READ = "SELECT * FROM " + TABLE_NAME + "," + USER_TWEETS +
            " WHERE " + FIELD_ID + "=" + USER_TWEETS_TWEET_FK + " AND " +
              USER_TWEETS_USER_TAGED + "=@" + USER_TWEETS_USER_TAGED;
        
        private const string FIND_BY_USER = FIND_TWEETS_USER_READ + " AND " + 
              USER_TWEETS_IS_OWNER + "=@" + USER_TWEETS_IS_OWNER;


		protected override string getTableName()
		{
            return TABLE_NAME;
		}

        /// <summary>
        /// Crea una instancia de la clase User y la carga con 
        /// los datos contenidos en el objeto DataReader.
        /// </summary>
        /// <param name="dr">Objeto DataReader con los datos del User.</param>
        /// <returns>Objeto User con los datos cargados.</returns>
        protected override Tweet toEntity(OleDbDataReader dr)
        {
            Tweet entidad = new Tweet();
            entidad.Id = (int)dr[FIELD_ID];
            entidad.Message = dr[FIELD_MESSAGE].ToString();
            return entidad;
        }
        protected override void completeEntity(OleDbDataReader dr, Tweet entidad) {
            entidad.Owner = new User();
            Boolean hasRelations = true;
            try
            {
                Object a = dr[USER_TWEETS_USER_TAGED];
            }
            catch (IndexOutOfRangeException e)
            {
                hasRelations = false;
            }
            if (hasRelations)
            {
                bool isOwner = (Boolean)dr[USER_TWEETS_IS_OWNER];
                if (isOwner)
                {
                    entidad.Owner.Username = dr[USER_TWEETS_USER_TAGED].ToString();
                }
                else
                {
                    User user = new User();
                    user.Username = dr[USER_TWEETS_USER_TAGED].ToString();
                    entidad.Taged.Add(user);
                }
            }
        }
        protected override OleDbCommand createOperationCommand(OperationType operation, Tweet entidad)
        {
            String sql = getSql(operation);
            Dictionary<String, Object> parameters = new Dictionary<String, Object> { 
            { "@"+FIELD_MESSAGE, entidad.Message } 
            };
            if (operation.Equals(OperationType.UPDATE)) {
                parameters.Add("@" + FIELD_ID, entidad.Id);
            }
            return createCommand(sql, parameters);
        }

        private string getSql(OperationType type)
        {
            string sql = "";
            if (OperationType.INSERT.Equals(type))
            {
                sql = "INSERT INTO " + getTableName() + " (" + FIELD_MESSAGE + ")" +
                "values(@" + FIELD_MESSAGE + ")";
            }
            else
            {
                sql = "UPDATE " + getTableName() +
                    " set(" + FIELD_MESSAGE + "=@" + FIELD_MESSAGE + ") where " +
                              FIELD_ID + "=@" + FIELD_ID;
            }
            return sql;
        }
		
		protected override string getIdColumn()
		{
			return FIELD_ID;
		}
        
        public List<Tweet> findByUserWrite(string userName)
        {
            List<Tweet> tweets = null;
            try
            {
                Dictionary<String, Object> parameters = new Dictionary<String, Object> { { "@" + USER_TWEETS_USER_TAGED, userName }, { "@" + USER_TWEETS_IS_OWNER, true } };
                OleDbCommand command = createCommand(FIND_BY_USER, parameters);
                tweets = find(command);
            }
            catch (ErrorConnectionException e) {
                throw e;
            }
            catch (Exception ex)
            {
                // En caso de que se produzca un error, se lo lanza hacia la
                // capa superior.
                throw new ObjectNotFoundException(ex, "El usuario: " + userName + " no existe");
            }
            return tweets;
        }
        
        public List<Tweet> findByUserRead(string userName)
        {
            List<Tweet> tweets = null;
            try
            {
                Dictionary<String, Object> parameters = new Dictionary<String, Object> { { "@" + USER_TWEETS_USER_TAGED, userName }};
                OleDbCommand command = createCommand(FIND_TWEETS_USER_READ, parameters);
                tweets = find(command);
            }
            catch (ErrorConnectionException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                // En caso de que se produzca un error, se lo lanza hacia la
                // capa superior.
                throw new ObjectNotFoundException(ex, "El usuario: " + userName + " no existe");
            }
            return tweets;

        }

        public override Object insert(Tweet tweet) {
          Object tweetId = base.insert(tweet);
          List<User> tageds = tweet.Taged;
          if (tageds != null)
          {      
              foreach (User user in tageds)
              {       
                  tagedUser(user.Username, tweetId, false);
              }
          }
          tagedUser(tweet.Owner.Username, tweetId, true);
          return tweetId;
        }

        private void tagedUser(String user, Object tweetId, Boolean isOwner)
        {
            OleDbCommand command = null;
            Dictionary<String, Object> parameters = new Dictionary<string, object> { 
              {"@"+ USER_TWEETS_TWEET_FK, tweetId},
              {"@" + USER_TWEETS_USER_TAGED, user},
              {"@" + USER_TWEETS_IS_OWNER, isOwner},
            };
            try
            {
                command = createCommand(INSERT_RELACION, parameters);
                command.ExecuteNonQuery();
            }
            finally
            {
                closeConnection(null, command);
            }
        }
    }
}
